﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections;

/// <summary>
/// 数据库操作接口类
/// </summary>
public class DBControl
{
    //数据库连接字符串
    string connstr = ConfigurationManager.AppSettings["connstr"];

    //获得数据库连接
    public SqlConnection GetConn()
    {
        SqlConnection conn = new SqlConnection(connstr);
        conn.Open();
        return conn;
    }

    //无返回集的方法
    public int SqlExecuteNonQuery(string strCMD)
    {
        SqlConnection conn = this.GetConn();
        SqlCommand cmd = new SqlCommand(strCMD,conn);
        int iResult = cmd.ExecuteNonQuery();
        conn.Close();
        conn.Dispose();
        cmd.Dispose();
        return iResult;
    }

    //返回SqlDataReader对象
    public SqlDataReader GetDataReader(string strCMD)
    {
        SqlConnection conn = this.GetConn();
        SqlCommand cmd = new SqlCommand(strCMD,conn);
        SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        return dr;
    }

    //返回数据集DataSet
    public DataSet GetDataSet(string strCMD,string strTableName)
    {
        SqlConnection conn = this.GetConn();
        SqlCommand cmd = new SqlCommand(strCMD,conn);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds,strTableName);
        conn.Close();
        conn.Dispose();
        da.Dispose();
        return ds;
    }

    //向数据库提交多个SQL命令，对数据进行事务管理（事务：这一批SQL命令要么都执行，都么都不执行）
    public bool ExecuteSQLs(ArrayList strCMDs)
    {
        bool bIsSuccess = true;
        SqlConnection conn = this.GetConn();
        SqlCommand cmd = new SqlCommand();
        SqlTransaction trans = conn.BeginTransaction();//启动事务管理
        cmd.Connection = conn;
        cmd.Transaction = trans;
        try
        {
            foreach (string strCMD in strCMDs)
            {
                cmd.CommandText = strCMD;
                cmd.ExecuteNonQuery();
            }
            trans.Commit();//提交数据事务
        }
        catch
        {
            bIsSuccess = false;
            //发生异常后，利用事务的回滚方法撤销所有SQL命令，以保证数据的完整性
            trans.Rollback();
        }
        finally
        {
            conn.Close();
            conn.Dispose();
            cmd.Dispose();
        }
        return bIsSuccess;
    }
}
